package com.xy.system.source.act;

import com.xy.system.source.IDataSourceServer;
import com.xy.system.source.vo.DataSourceVO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class SqlServerAction implements IDataSourceServer {

    public String getServerName() {
        return "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    }

    public void fillDbName(DataSourceVO cond) {
        //jdbc:sqlserver://192.168.30.150:1433;DatabaseName=pub_plat_tj_test_system;encrypt=false
        String url = cond.getUrl();
        String newUrl = url.replaceAll("\\\\", "/");
        cond.setUrl(newUrl);
        int begin = newUrl.indexOf("=");
        if (begin < 0) {
            throw new RuntimeException("url错误");
        }
        int end = newUrl.indexOf(";", begin);
        if (end < 0) {
            throw new RuntimeException("url错误");
        }
        String name = newUrl.substring(begin + 1).trim();
        cond.setDbName(name);
    }

    @Override
    public List<String> getAllTableMap(Connection conn, DataSourceVO dataSource) {
        List<String> tableNames = new ArrayList<>();
        Statement st = null;
        ResultSet rs = null;
        try {
            st = conn.createStatement();
            rs = st.executeQuery("select name as table_name from sys.tables order by name");
            while (rs.next()) {
                tableNames.add(rs.getString(1));
            }
            return tableNames;
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (st != null) {
                try {
                    st.close();
                } catch (Exception e1) {

                }
            }
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception e2) {

                }
            }
        }

        return null;
    }

    @Override
    public Map<String, String> getRemarkNameMap(Connection conn, DataSourceVO dataSource, String tableName) {
        Map<String, String> remarkMap = new HashMap<>();
        Statement st = null;
        ResultSet rs2 = null;
        try {
            st = conn.createStatement();
            StringBuilder sql = new StringBuilder();
            sql.append(" SELECT ");
            sql.append("    b.name AS column_name ");
            sql.append("    ,c.value AS column_comment ");
            sql.append(" FROM  sys.tables a left join sys.columns b on a.object_id=b.object_id ");
            sql.append(" LEFT JOIN sys.extended_properties c on b.object_id=c.major_id and b.column_id=c.minor_id ");
            sql.append(" WHERE a.type='U'  and a.name = '").append(tableName.toLowerCase()).append("' ");
            rs2 = st.executeQuery(sql.toString());
            while (rs2.next()) {
                remarkMap.put(rs2.getString(1), rs2.getString(2));
            }
            return remarkMap;
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (st != null) {
                try {
                    st.close();
                } catch (Exception e1) {

                }
            }
            if (rs2 != null) {
                try {
                    rs2.close();
                } catch (Exception e2) {

                }
            }
        }

        return null;
    }
}
